sum_stat_drg_diem <- function(data = dt_adm[max_cost_row ==1 ],
                              name,
                              var = new_cat) {
  data %>% 
    mutate(new_cat = as.character(main_cat)) %>% 
    mutate(new_cat =  factor(case_when(category=="differential" ~ "Inpatient_DRG",
                                       category%in%c("hospitalization_urgent",
                                                     "hospitalization_elective") ~ "Inpatient_PerDiem",
                                       TRUE ~ new_cat))) %>% 
    mutate(days =  total_event_length_days) %>% 
    group_by({{var}}) %>% 
    summarise(avg_cost = mean(sum_cost),
              count = n(),
              avg_days = mean(days)) %>% 
    mutate(share_of_adm = count/sum(count),
           cost_per_day = avg_cost / avg_days) %>% 
    mutate(group = name) %>% 
    select(group,{{var}},avg_cost,share_of_adm,count,avg_days,cost_per_day)
}






paste_adm_proc <- function(con, 
                                 data_adm = dt_adm[max_cost_row == 1 ]) { 


  adm_proc <- tbl(con, dbplyr::in_schema("sql_query")) %>%
    mutate(zihui_bikur = as.character(zihui_ishpuz_bikur)) %>% 
    collect() %>% 
    data.table()
  
dt_adm_proc<- merge(x = data_adm, 
                    y = adm_proc , 
                     by = c("zihui_bikur","id_var"),
                     all.x = T,
                     all.y = F
                )

dt_adm_proc[,clalit := if_else(is.na(Maintanance), 0, 1)]


get_cnr_adm_test <- dt_adm_proc %>%
  mutate(main_cat = "Inpatient") %>% 
  group_by(zihui_bikur) %>% 
  mutate(numrow = n()) %>% 
  ungroup() %>% 
  mutate(type = factor(case_when(clalit  == 0 ~ "External",
                                 Surgery  == 1 ~ "Surgery" ,
                                 Chemotherapy  == 1 ~ "Chemotherapy",
                                 Radiation  == 1 ~ "Radiation",
                                 TRUE   ~ "Maintenance"),
                       levels = c("External","Surgery" ,"Chemotherapy" , "Radiation","Maintenance" ) ))

return(get_cnr_adm_test)
}

do_table_2_procedure <- function(get_cnr_adm_test) {

library(knitr)
library(kableExtra)
options(knitr.kable.NA = '.')

sum_stat_drg <- 
  sum_stat_drg_diem(get_cnr_adm_test, "All", new_cat) %>% 
  add_row(sum_stat_drg_diem(get_cnr_adm_test  %>% filter(DMG_died_within_365d=="1"),
                            "Decedent",new_cat)) %>% 
  add_row(sum_stat_drg_diem(get_cnr_adm_test  %>% filter(DMG_died_within_365d=="0"),
                            "Survivor",new_cat))  %>% 
  group_by(group) %>% 
  mutate(" " = group,
         "Billing Method" = ifelse(new_cat=="Inpatient_DRG","Procedure Based","Per Diem"), 
         "Average Cost\nPer Day (NIS)" = scales::comma(cost_per_day),
         "Avg Length\nof Stay" = round(avg_days,1),
         "Share of Admissions" = round(count/sum(count)*100,1))  %>% 
  ungroup() %>% 
  select(8:12) 
  
  names_sum_stat_drg <-  names(sum_stat_drg) 
  names(sum_stat_drg) <- c("","","(1)", "(2)", "(3)")

sum_stat_drg %>% 
  kable("latex", booktabs = T, align = c("l","l", rep("r", ncol(.)-2))) %>%
  kable_styling(full_width = F ) %>%
  add_header_above(names_sum_stat_drg,underline = F,line = F) %>%
  collapse_rows(1, latex_hline = "none", valign = "top") %>%
  row_spec(0, align = "c") %>% 
  write("sum_stats_drg_diem.tex")

sum_stat_procedure <- sum_stat_drg_diem(get_cnr_adm_test , "All", type) %>% 
  add_row(sum_stat_drg_diem(get_cnr_adm_test  %>% filter(DMG_died_within_365d=="1"),
                            "Decedent",type)) %>% 
  add_row(sum_stat_drg_diem(get_cnr_adm_test  %>% filter(DMG_died_within_365d=="0"),
                            "Survivor",type)) %>% 
  filter(type  != "External" ) %>% 
  group_by(group) %>% 
  mutate(" " = group,
         "Main Procedure" = type, 
         "Average Cost\nPer Day (NIS)" = scales::comma(cost_per_day),
         "Avg Length\nof Stay" = round(avg_days,1),
         "Share of Admissions" = round(count/sum(count)*100,1))  %>% 
  arrange(-`Share of Admissions`, .by_group = T) %>% 
  ungroup() %>% 
  select(8:12)


names_sum_stat_procedure <-  names(sum_stat_procedure) 
names(sum_stat_procedure) <- c("","","(1)", "(2)", "(3)")

sum_stat_procedure %>% 
  kable("latex", booktabs = T, align = c("l","l", rep("r", ncol(.)-2))) %>%
  kable_styling(full_width = F ) %>%
  add_header_above(names_sum_stat_procedure,underline = F,line = F) %>%
  collapse_rows(1, latex_hline = "none", valign = "top") %>%
  row_spec(0, align = "c") %>% 
  write("sum_stats_proc.tex")
}
